select
    ind.name, ind.index_id,  col.name,ic.index_column_id,
    ind.*, ic.*, col.*
from 
    sys.indexes ind
inner join 
    sys.index_columns ic on 
      ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join
    sys.columns col on
      ic.object_id = col.object_id and ic.column_id = col.column_id 
inner join
    sys.tables t on 
      ind.object_id = t.object_id
where 
    ind.is_primary_key = 0 
    and ind.is_unique = 0 
    and ind.is_unique_constraint = 0
    and t.is_ms_shipped = 0
order by
    t.name, ind.name, ind.index_id, ic.index_column_id
    
    
go


SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],  
  T.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],  
  I.[type_desc], I.[is_unique], I.[data_space_id], I.[ignore_dup_key], I.[is_primary_key], 
  I.[is_unique_constraint], I.[fill_factor],    I.[is_padded], I.[is_disabled], I.[is_hypothetical], 
  I.[allow_row_locks], I.[allow_page_locks], IC.[is_descending_key], IC.[is_included_column] 
FROM sys.[tables] AS T  
  INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]  
  INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] 
  INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] 
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP' 

ORDER BY T.[name], I.[index_id], IC.[key_ordinal]


go

SELECT S.NAME SCHEMA_NAME,T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME COLUMN_NAME
  FROM SYS.TABLES T
       INNER JOIN SYS.SCHEMAS S
    ON T.SCHEMA_ID = S.SCHEMA_ID
       INNER JOIN SYS.INDEXES I
    ON I.OBJECT_ID = T.OBJECT_ID
       INNER JOIN SYS.INDEX_COLUMNS IC
    ON IC.OBJECT_ID = T.OBJECT_ID
       INNER JOIN SYS.COLUMNS C
    ON C.OBJECT_ID  = T.OBJECT_ID
   AND IC.INDEX_ID    = I.INDEX_ID
   AND IC.COLUMN_ID = C.COLUMN_ID
 WHERE 1=1

ORDER BY I.NAME,I.INDEX_ID,IC.KEY_ORDINAL


go
